<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateBankDetailsHangzhouTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('bank_details_hangzhou', function (Blueprint $table) {
            $table->id()->comment('主键ID');

            // 银行返回的原始数据字段（基于实际API响应）
            $table->string('serial_no', 50)->unique()->comment('银行流水号（serialNo）');
            $table->string('serial_id', 50)->unique()->comment('银行序列ID（serialId）');
            $table->string('voucher_no', 50)->nullable()->comment('凭证号（voucherNo）');
            $table->date('trans_date')->comment('交易日期（从transDate解析）');
            $table->time('trans_time')->comment('交易时间（从transTime解析）');
            $table->datetime('transaction_datetime')->comment('完整交易时间（transDate+transTime）');
            $table->string('bank_acc', 50)->comment('银行账号（bankAcc）');
            $table->string('acc_name')->comment('账户名称（accName）');
            $table->decimal('amt', 15, 2)->comment('交易金额（amt）');
            $table->decimal('bal', 15, 2)->comment('余额（bal）');
            $table->string('cd_sign', 1)->comment('收支标识（cdSign）0-收入,1-支出');
            $table->string('cur', 10)->default('人民币')->comment('币种（cur）');
            $table->string('opp_acc_name')->nullable()->comment('对方账户名称（oppAccName）');
            $table->string('opp_acc_no', 50)->nullable()->comment('对方账号（oppAccNo）');
            $table->string('opp_acc_bank')->nullable()->comment('对方银行（oppAccBank）');
            $table->string('bank_name')->nullable()->comment('银行名称（bankName）');
            $table->text('abs')->nullable()->comment('摘要/备注（abs）');
            $table->string('uses')->nullable()->comment('用途（uses）');

            // 匹配状态管理
            $table->enum('match_status', ['未匹配', '已匹配', '已忽略'])->default('未匹配')->comment('匹配状态');
            $table->unsignedBigInteger('matched_recharge_id')->nullable()->comment('匹配的充值申请ID');
            $table->decimal('match_confidence', 3, 2)->default(0.00)->comment('匹配置信度 0.00-1.00');
            $table->timestamp('matched_at')->nullable()->comment('匹配时间');
            $table->unsignedBigInteger('matched_by')->nullable()->comment('匹配操作人');

            // 同步信息
            $table->string('sync_batch_id', 100)->comment('同步批次ID');
            $table->json('raw_data')->comment('银行返回的原始JSON数据');
            $table->timestamp('synced_at')->useCurrent()->comment('同步时间');

            // 时间戳
            $table->timestamp('created_at')->useCurrent()->comment('创建时间');
            $table->timestamp('updated_at')->useCurrent()->useCurrentOnUpdate()->comment('更新时间');

            // 索引优化
            $table->index(['bank_acc', 'trans_date'], 'idx_account_date');
            $table->index(['amt', 'trans_date'], 'idx_amount_date');
            $table->index(['cd_sign', 'trans_date'], 'idx_sign_date');
            $table->index(['match_status'], 'idx_match_status');
            $table->index(['opp_acc_name'], 'idx_opp_name');
            $table->index(['sync_batch_id'], 'idx_sync_batch');
            $table->index(['transaction_datetime'], 'idx_transaction_datetime');

            // 外键约束
            $table->foreign('matched_recharge_id')->references('id')->on('recharges')->onDelete('set null');
            $table->foreign('matched_by')->references('id')->on('admin_users')->onDelete('set null');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('bank_details_hangzhou');
    }
}
